/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package sys.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import sys.bean.CaixaBean;
import sys.bean.UsuarioBean;
import sys.utils.ConexaoADS;
import static sys.utils.Utils.dataCorreta;
import static sys.utils.Utils.horaCorreta;

/**
 *
 * @author Administrador
 */
public class CaixaDao {
    
    public static List<CaixaBean> sangrias(String data) throws SQLException, IOException {
        List<CaixaBean> listaUsuarios = new ArrayList<>();
        Connection con = ConexaoADS.getConection();
        
        String sql = "SELECT TCNTRA, TCHORA, TCNTER, C04NOME, TCOBS \n"
                + "FROM TC" + data + " \n"
                + "INNER JOIN SPAC04 ON (TCNCXA = C04CODIGO) \n"
                + "WHERE TCTIPO = '04' \n"
                + "ORDER BY 1";
        
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        
        while (rs.next()) {
            
            CaixaBean c = new CaixaBean();
            c.setTransacao(rs.getString("TCNTRA"));
            c.setHora(horaCorreta(rs.getString("TCHORA")));
            c.setTerminal(rs.getString("TCNTER"));
            c.setObs(rs.getString("TCOBS"));
            
            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));
            
            c.setUsuario(usuario);
            
            listaUsuarios.add(c);
        }
        
        stmt.close();
        con.close();
        
        return listaUsuarios;
    }
    
    public static List<CaixaBean> listaUsuarios(String tabela) throws SQLException, IOException {
        List<CaixaBean> listaUsuarios = new ArrayList<>();
        Connection con = ConexaoADS.getConection();
        
        String sql = "SELECT ACNTER, C04NOME, ACTIPO, C04CODIGO \n"
                + "FROM AC" + tabela + " \n"
                + "INNER JOIN SPAC04 ON (C04CODIGO = ACNCXA)\n"
                + "WHERE ACTIPO = 'F'";
        
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        
        while (rs.next()) {
            
            CaixaBean sp = new CaixaBean();
            
            sp.setTerminal(rs.getString("ACNTER"));
            
            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));
            usuario.setCodigo(rs.getString("C04CODIGO"));
            sp.setUsuario(usuario);
            
            listaUsuarios.add(sp);
        }
        
        stmt.close();
        con.close();
        
        return listaUsuarios;
    }
    
    public static CaixaBean caixaDiaAnterior(String user, String tabela) throws SQLException, IOException {
        
        Connection con = ConexaoADS.getConection();
        String sql = "SELECT TOP 1 SCDATA, C04NOME, SCSALDO \n"
                + "FROM SC" + tabela + " \n"
                + "INNER JOIN SPAC04 ON (C04CODIGO = SCNCXA)\n"
                + "WHERE SCNCXA = '" + user + "'";
        
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        
        CaixaBean caixa = new CaixaBean();
        
        while (rs.next()) {
            
            caixa.setData(dataCorreta(rs.getString("SCDATA")));
            
            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));
            
            caixa.setUsuario(usuario);
            
            caixa.setSaldo1(rs.getDouble("SCSALDO"));
            
        }
        
        con.close();
        stmt.close();
        
        return caixa;
    }
    
    public static CaixaBean caixaDiaAtual(String user, String tabela) throws SQLException, IOException {
        
        Connection con = ConexaoADS.getConection();
        String sql = "SELECT TOP 1 MCVALOR  \n"
                + "FROM MC" + tabela + " \n"
                + "INNER JOIN TC" + tabela + " ON (MCNTRA = TCNTRA) \n"
                + "WHERE TCNCXA = '" + user + "'";
        
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        CaixaBean caixa = new CaixaBean();
        
        while (rs.next()) {
            
            caixa.setSaldo2(rs.getDouble("MCVALOR"));
            
        }
        
        con.close();
        stmt.close();
        
        return caixa;
    }
    
    public static List<CaixaBean> listaMovimentaxao(String tabela, String user) throws SQLException, IOException {
        List<CaixaBean> listaUsuarios = new ArrayList<>();
        Connection con = ConexaoADS.getConection();
        
        String sql = "SELECT TCNTRA, TCHORA, C04NOME, MCMOEDA, MCVALOR, TCOBS \n"
                + "FROM TC" + tabela + " \n"
                + "INNER JOIN MC" + tabela + " ON (TCNTRA = MCNTRA) \n"
                + "INNER JOIN SPAC04 ON (C04CODIGO = TCNCXA) \n"
                + "WHERE TCTIPO IN ('01','03', '04') \n"
                + "AND TCNCXA = '" + user + "'";
        
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        
        while (rs.next()) {
            
            CaixaBean caixa = new CaixaBean();
            
            caixa.setTransacao(rs.getString("TCNTRA"));
            caixa.setHora(horaCorreta(rs.getString("TCHORA")));
            
            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));
            caixa.setUsuario(usuario);
            
            if (rs.getString("MCMOEDA").equals("D")) {
                caixa.setMoeda("DINHEIRO");
            }
            if (rs.getString("MCMOEDA").equals("C")) {
                caixa.setMoeda("CHEQUE");
            }
            if (rs.getString("MCMOEDA").equals("O")) {
                caixa.setMoeda("OUTRAS");
            }
            caixa.setSaldo1(rs.getDouble("MCVALOR"));
            caixa.setObs(rs.getString("TCOBS"));
            
            listaUsuarios.add(caixa);
        }
        
        stmt.close();
        con.close();
        return listaUsuarios;
    }

    /*
     metodo utilizado para trazer os dados do responsavel por gerar um vale mercadoria
     */
    public static CaixaBean dadosValeMercadoria(String dataTabela, String nTransacao) throws SQLException, IOException {
        
        Connection con = ConexaoADS.getConection();
        String sql = "SELECT TCNTER, C04NOME, TCHORA, TCNPED, TCVLR \n"
                + "FROM TC" + dataTabela + " \n"
                + "INNER JOIN SPAC04 ON (TCNCXA = C04CODIGO)\n"
                + "WHERE \n"
                + "TCNTRA = '" + nTransacao + "'";
        
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        
        CaixaBean caixa = new CaixaBean();
        
        while (rs.next()) {
            
            caixa.setTerminal(rs.getString("TCNTER"));
            caixa.setHora(horaCorreta(rs.getString("TCHORA")));
            
            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));
            
            caixa.setUsuario(usuario);
        }
        
        con.close();
        stmt.close();
        
        return caixa;
    }
    
}
